package cn.myfirefly.utils;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import cn.myfirefly.core.Const;

/**
 * 导出功能
 * 
 * 根据导出功能配置，将数据结果集导出为excel文件并下载。
 * 
 * @author zhouxiaoming
 *
 */
public class ExportUtil {
	private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);
	
	private static final String DATA_TYPE_DATE = "date";
	private static final String DATA_TYPE_DATE_TIME = "datetime";
	private static final String DATA_TYPE_TIMESTAMP = "timestamp";
	private static final String DATA_TYPE_INT = "int";
	private static final String DATA_TYPE_BIGINT = "bigint";
	private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd";
	
	/**
	 * 检查下载最大行数限制
	 * @param configKey
	 * @param rowCount
	 * @return 如果为空值，说明检查通过，如果不为空值，则说明已超过最大行数限制
	 */
	public static String checkMaxRow(String configKey, int rowCount) {
		StringBuffer msg = new StringBuffer();
		JSONObject config = null;
		try {
			config = getConfig(configKey);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		Integer maxRow = null;
		if (config != null) {
			maxRow = config.getInteger("maxRow");
		}
		
		if (maxRow == null) {
			maxRow = PropUtil.use(Const.Config.CONFIG_FILE).getInt(Const.Config.EXPORT_ROWS_MAX);
		}
		
		if (maxRow != null) {
			if (rowCount > maxRow.intValue()) {
				msg.append("文件过大无法下载，建议重新设置查询项");
			}
		}
		
		return msg.toString();
	}
	
	@SuppressWarnings("rawtypes")
	public static String export(List result, String configKey, String fileName, HttpServletRequest request, HttpServletResponse response) {
		String rootPath = request.getSession().getServletContext().getRealPath("/");
		String path= rootPath + "/WEB-INF/export_template/" + configKey + ".xlsx";
		File template = new File(path);
		return export(result, configKey, fileName, template, response);
	}
	
	@SuppressWarnings("rawtypes")
	public static String export(List result, String configKey, String fileName, File template, HttpServletResponse response) {
		StringBuffer msg = new StringBuffer();
		JSONObject config = null;
		try {
			config = getConfig(configKey);
		} catch (SQLException e) {
			msg.append("读取配置信息失败");
			logger.error(e.getMessage(), e);
		}
		
		if (config != null) {
			XSSFWorkbook wb = null;
			try {
				wb = new XSSFWorkbook(template);
			} catch (InvalidFormatException e) {
				msg.append("文件格式错误！");
				logger.error(e.getMessage(), e);
			} catch (IOException e) {
				msg.append("读取模板文件错误！");
				logger.error(e.getMessage(), e);
			}
			if (wb != null) {
				try {
					Sheet sheet =  wb.getSheetAt(0);
					int dataRow = config.getIntValue("dataRow");
					int startColumn = config.getIntValue("startColumn");
					JSONArray columns = config.getJSONArray("columns");
					
					int size = result.size();
					for (int i = 0; i < size; i++) {
						handRowData(result.get(i), columns, sheet, dataRow+i, startColumn);
					}
					
					response.setContentType("application/ms-excel;charset=UTF-8");
					response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");
					OutputStream out = response.getOutputStream();
					wb.write(out);
					out.flush();
					out.close();
				} catch (Exception e) {
					msg.append("导出文件失败！");
					logger.error(e.getMessage(), e);
				}
			}
		}
		
		return msg.toString();
	}
	
	@SuppressWarnings("unchecked")
	private static void handRowData(Object object, JSONArray columns, Sheet sheet, int rowIdx, int startColumn) throws Exception {
		Map<String, Object> map = null;
		if (object instanceof Map<?, ?>) {
			map = (Map<String, Object>)object;
		}
		
		Row row = createRow(sheet, rowIdx);
		JSONObject column = null;
		for (int i = 0; i < columns.size(); i++) {
			column = columns.getJSONObject(i);
			JSONObject fromTable = column.getJSONObject("fromTable");
			String colVal = getValue(map, object, column.getString("name"), column);
			if (fromTable != null) {
				colVal = getValueFromTable(fromTable, colVal, column);
			}
			createCellAndSetValue(row, startColumn+i, colVal);
		}
	}
	
	private static Row createRow(Sheet sheet, int rowIdx) {
		Row row = sheet.getRow(rowIdx);
		if(row == null) {
			row = sheet.createRow(rowIdx);
		}
		return row;
	}
	
	private static void createCellAndSetValue(Row row, int colIdx, String value) {
		Cell cell = row.getCell(colIdx);
		if(cell == null) {
			cell = row.createCell(colIdx);
		}
		cell.setCellValue(value);
	}
	
	private static String getValue(Map<String, Object> map, Object object, String key, JSONObject column) throws Exception {
		Object obj = null;
		if (map != null) {
			obj = map.get(key);
		} else {
			obj = ObjectUtil.getFieldValue(object, key);
		}
		
		if (obj != null) {
			String dataType = column.getString("dataType");
			String format = column.getString("format");
			String value = null;
			if (StrUtil.equals(dataType, DATA_TYPE_DATE) || StrUtil.equals(dataType, DATA_TYPE_DATE_TIME) || StrUtil.equals(dataType, DATA_TYPE_TIMESTAMP)) {
				if (StrUtil.notBlank(format)) {
					value = DateUtil.formatDateTime((Date)obj, format);
				} else {
					value = DateUtil.formatDateTime((Date)obj, DEFAULT_DATE_FORMAT);
				}
			} else {
				value = obj.toString();
			}
			return value;
		}
		
		return "";
	}
	
	private static String getValueFromTable(JSONObject fromTable, String colVal, JSONObject column) {
		Object realValue = null;
		boolean isDB = fromTable.getBoolean("isDB") == null ? true : fromTable.getBoolean("isDB");
		if (isDB) {
			JSONArray params = fromTable.getJSONArray("params");
			StringBuffer sql = new StringBuffer();
			sql.append(" select ");
			sql.append(fromTable.getString("column"));
			sql.append(" from ");
			sql.append(fromTable.getString("table"));
			sql.append(" where ");
			
			List<Object> objects = new ArrayList<Object>();
			for (int i = 0; i < params.size(); i++) {
				sql.append(params.getJSONObject(i).getString("key"));
				String dataType = params.getJSONObject(i).getString("dataType");
				String format = params.getJSONObject(i).getString("format");
				String val = params.getJSONObject(i).getString("value");
				if (val.startsWith("${")) {
					if (StrUtil.equals(val, "${this}")) {
						objects.add(convertDataType(colVal, dataType, format));
					} else {
						objects.add(convertDataType(val, dataType, format));
					}
				} else {
					objects.add(convertDataType(val, dataType, format));
				}
				
				if (i + 1 < params.size()) {
					sql.append(" = ? and ");
				} else {
					sql.append(" = ? ");
				}
			}
			
			DataSource dataSource = (DataSource)SpringContextUtil.getBean("dataSource");
			try {
				realValue = queryUnique(dataSource, sql.toString(), objects);
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
			
		} else {
			JSONArray table = fromTable.getJSONArray("table");
			JSONObject tableItem = null;
			for (int i = 0; i < table.size(); i++) {
				tableItem = table.getJSONObject(i);
				if (StrUtil.equals(colVal, tableItem.getString("value"))) {
					realValue = tableItem.getString("label");
				}
			}
		}
		
		if (realValue != null) {
			return realValue.toString();
		}
		return "";
	}
	
	private static Object convertDataType(String inputValue, String dataType, String format) {
		Object realValue = null;
		if (StrUtil.notBlank(inputValue)) {
			if (StrUtil.equals(dataType, DATA_TYPE_BIGINT)) {
				try {
					realValue = Long.valueOf(inputValue);
				} catch (NumberFormatException e) {
					logger.error(e.getMessage(), e);
				}
			} else if (StrUtil.equals(dataType, DATA_TYPE_INT)) {
				try {
					realValue = Integer.valueOf(inputValue);
				} catch (NumberFormatException e) {
					logger.error(e.getMessage(), e);
				}
			} else if (StrUtil.equals(dataType, DATA_TYPE_DATE) || StrUtil.equals(dataType, DATA_TYPE_DATE_TIME) || StrUtil.equals(dataType, DATA_TYPE_TIMESTAMP)) {
				if (StrUtil.notBlank(format)) {
					realValue = DateUtil.str2Date(inputValue, format);
				} else {
					realValue = DateUtil.str2Date(inputValue, DEFAULT_DATE_FORMAT);
				}
			} else {
				realValue = inputValue;
			}
		} else {
			realValue = inputValue;
		}
		return realValue;
	}
	
	private static JSONObject getConfig(String configKey) throws SQLException {
		DataSource dataSource = (DataSource)SpringContextUtil.getBean("dataSource");
		String sql = "select config_value from mm_export_config where config_key=? ";
		String retval = (String)queryUnique(dataSource, sql, configKey);
		return retval != null ? JSON.parseObject(retval) : null;
	}
	
	private static Object queryUnique(DataSource dataSource, String sql, Object...objects) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String retval = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				stmt.setObject(1+i, objects[i]);
			}
			
			rs = stmt.executeQuery();
			if(rs.next()) {
				retval = rs.getString(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(conn, stmt, rs);
		}
		
		return retval;
	}
	
	private static Object queryUnique(DataSource dataSource, String sql, List<Object> objects) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Object retval = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < objects.size(); i++) {
				stmt.setObject(1+i, objects.get(i));
			}
			
			rs = stmt.executeQuery();
			if(rs.next()) {
				retval = rs.getObject(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(conn, stmt, rs);
		}
		
		return retval;
	}
	
	public static void close(Connection con, Statement stmt, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			rs = null;
		} catch (SQLException e) {
		}
		try {
			if (stmt != null) {
				stmt.close();
			}
			stmt = null;
		} catch (SQLException e) {
		}
		try {
			if (con != null && !con.isClosed()) {
				con.close();
			}
			con = null;
		} catch (SQLException e) {
		}
	}
}
